<h1 align = 'center'>EXPENDITURES</h1>
<?php    
    $this->load->database();
    $dateListQ = 'SELECT DISTINCT DateTransaction FROM pf_expenditure GROUP BY DateTransaction desc';
    $dateListR = $this->db->query($dateListQ);
    if($dateListR->num_rows() > 0)
    {
        echo '<table align = "center" border = 5 width = "95%">';
        echo '<tr><th>Date</th><th>Transaction Type</th><th>c/o</th><th>Name</th><th>Facility Location</th><th>Facility Count</th><th>Price Per Item</th><th>Total Amount</th></tr>';
        foreach($dateListR->result() as $dateList)
        {
            $totalFacilityCount = 0;
    $totalPrice = 0;
            $dateRowCount = 0;
            $transactTypeQ = 'SELECT DISTINCT TransactType FROM pf_expenditure WHERE DateTransaction = \''.$dateList->DateTransaction.'\'';
            $transactTypeR = $this->db->query($transactTypeQ);
            
            foreach($transactTypeR->result() as $transactType)
            {
                $nameCountQ = 'SELECT COUNT(ExpenditureID) as nameCount FROM pf_expenditure WHERE DateTransaction = \''.$dateList->DateTransaction.'\' AND TransactType = \''.$transactType->TransactType.'\'';
                $nameCountR = $this->db->query($nameCountQ);
                $nameCount = $nameCountR->result();
                $dateRowCount = $dateRowCount + $nameCount[0]->nameCount;
            }
            echo '<tr><td rowspan = '.($dateRowCount).' align = "center">'.$dateList->DateTransaction.'</td>';
            foreach($transactTypeR->result() as $transactType)
            {
                $nameListQ = 'SELECT pf_facilities.FacilityName, pf_facilities.RoomAssigned, pf_expenditure.ItemCount, pf_expenditure.PricePerItem, pf_expenditure.CareOf FROM pf_facilities, pf_expenditure WHERE pf_facilities.FacilityID = pf_expenditure.FacilityID AND pf_expenditure.DateTransaction = \''.$dateList->DateTransaction.'\' AND pf_expenditure.TransactType = \''.$transactType->TransactType.'\'';
                $nameListR = $this->db->query($nameListQ);
                echo '<td rowspan = '.($nameListR->num_rows()).' align = "center">'.$transactType->TransactType.'</td>';
                foreach($nameListR->result() as $nameList)
                {
                    echo '<td align = "center">'.$nameList->CareOf.'</td><td align = "center">'.$nameList->FacilityName.'</td><td align = "center">'.$nameList->RoomAssigned.'</td><td align = "center">'.$nameList->ItemCount.'</td><td align = "right">'.$nameList->PricePerItem.'</td><td align = "right">'.$nameList->ItemCount * $nameList->PricePerItem.'</td></tr>';
                    $totalFacilityCount = $totalFacilityCount + $nameList->ItemCount;
                    $totalPrice = $totalPrice + $nameList->PricePerItem;
                }
            }
            echo '<tr><td align = "right" colspan = 5><b>TOTAL FACILITIES:</b></td><td align = "right">'.$totalFacilityCount.'</td><td align = "right"><b>TOTAL PRICE:</b></td><td align = "right">'.$totalPrice.'</td></tr><tr><td colspan = 8></td></tr><tr><td colspan = 8></td></tr>';
            
        }
        echo '</table>';
    }
    else
    {
        echo '<h5 align = "center"><i>no expenditure report</i></h5>';
    }
?>